﻿Namespace DataAccess


    Public Class Words

        Public Shared Function TranslateDb(ByVal srcWord As String, _
                                           ByVal srcLanguage As String, _
                                           ByVal trgLanguage As String) As String

            Try

                Dim cnn As SqlServerCe.SqlCeConnection
                Dim cmd As SqlServerCe.SqlCeCommand
                Dim sql As String

                sql = "SELECT TargetWord FROM Words " + _
                      "Where SourceWord=@SourceWord AND SourceWordLang=@SourceWordLang AND " + _
                      "      TargetWordLang=@TargetWordLang"

                cnn = New SqlServerCe.SqlCeConnection(Base.ConnectionString)
                cmd = New SqlServerCe.SqlCeCommand(sql, cnn)

                cmd.Parameters.Add("@SourceWord", srcWord)
                cmd.Parameters(0).DbType = DbType.String

                cmd.Parameters.Add("@SourceWordLang", srcLanguage)
                cmd.Parameters(1).DbType = DbType.String

                cmd.Parameters.Add("@TargetWordLang", trgLanguage)
                cmd.Parameters(2).DbType = DbType.String


                cnn.Open()

                Dim strResult As String = String.Empty

                Dim reader As SqlServerCe.SqlCeDataReader = cmd.ExecuteReader
                Dim b As Boolean = reader.Read()

                If b Then
                    strResult = reader(0)
                End If

                cnn.Close()

                Return strResult

            Catch ex As Exception
                MsgBox(ex.Message.ToString, MsgBoxStyle.Critical, "Error")
                Return String.Empty
            End Try

        End Function


        Public Shared Sub AddToDb(ByVal srcWord As String, _
                                  ByVal srcLanguage As String, _
                                  ByVal trgWord As String, _
                                  ByVal trgLanguage As String)

            Try

            
                If srcWord.Length > 100 Then
                    srcWord = srcWord.Substring(0, 100)
                End If

                If Not String.IsNullOrEmpty(TranslateDb(srcWord, srcLanguage, trgLanguage)) Then
                    Exit Sub
                End If


                Dim cnn As SqlServerCe.SqlCeConnection
                Dim cmd As SqlServerCe.SqlCeCommand
                Dim sql As String

                sql = "INSERT INTO Words SELECT @SourceWord,@TargetWord,@SourceWordLang,@TargetWordLang"
                cnn = New SqlServerCe.SqlCeConnection(Base.ConnectionString)
                cmd = New SqlServerCe.SqlCeCommand(sql, cnn)

                cmd.Parameters.Add("@SourceWord", srcWord)
                cmd.Parameters(0).DbType = DbType.String

                cmd.Parameters.Add("@SourceWordLang", srcLanguage)
                cmd.Parameters(1).DbType = DbType.String

                cmd.Parameters.Add("@TargetWord", trgWord)
                cmd.Parameters(2).DbType = DbType.String

                cmd.Parameters.Add("@TargetWordLang", trgLanguage)
                cmd.Parameters(3).DbType = DbType.String

                cnn.Open()
                cmd.ExecuteNonQuery()
                cnn.Close()

            Catch ex As Exception
                MsgBox(ex.Message.ToString, MsgBoxStyle.Critical, "Error")
            End Try

        End Sub

    End Class

 

End Namespace